/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */

package com.model.service;

import com.model.domain.ubicacion.Departamento;
import com.model.domain.ubicacion.Odei;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;


/**
 *
 * @author Rdelacruz
 */
public class OdeiService 
extends AbstractService
{
    private DepartamentoService departamentoService;
    
    public Odei findConDepartamentoByCodigo(Connection con, String codigo) throws SQLException {
        Odei odei = this.findByCodigo(con, codigo);       
        if(odei!=null)
            odei.setDepartamento(departamentoService.findByCodigo(con, odei.getDepartamento().getCodigo()));
        return odei;
    }

    public Odei findByCodigo(Connection con, String codigo) throws SQLException {
        Statement st = null;
        try {                        
            st = con.createStatement();
            String sql = "select CodOdei, NomOdei, ccdd from Odei where CodOdei='"+codigo+"'";
            ResultSet rs = st.executeQuery(sql);            
            if(rs.next()) {
                Odei obj = new Odei();
                obj.setCodigo(rs.getString(1));
                obj.setNombre(rs.getString(2).toUpperCase());                
                Departamento dpto = new Departamento();                        
                obj.setDepartamento(dpto);
                dpto.setCodigo(rs.getString(3));
                return obj;
            }
        }
        finally {
            if(st!=null) st.close();
        }
        
        return null;
    }
    
    public List<Odei> findByCodigoDepartamento(Connection con, String codigoDepartamento) throws SQLException {
        List<Odei> lista = new ArrayList<Odei>();
        if(codigoDepartamento==null || codigoDepartamento.isEmpty())
            return lista;
        
        if(codigoDepartamento.equals("07"))//Callao
            codigoDepartamento = "15";//Para que traiga las ODEI de Lima
            
        Statement st = null;
        try {                        
            st = con.createStatement();
            String sql = "select CodOdei, NomOdei from Odei where ccdd='"+codigoDepartamento+"'";
            ResultSet rs = st.executeQuery(sql);            
            while(rs.next()) {
                Odei obj = new Odei();
                obj.setCodigo(rs.getString(1));
                obj.setNombre(rs.getString(2).toUpperCase());
                lista.add(obj);
            }
        }
        finally {
            if(st!=null) st.close();
        }
        return lista;
    }
    
    public List<Odei> findAll(Connection con) throws SQLException {
        List<Odei> lista = new ArrayList<Odei>();
        Statement st = null;
        try {                        
            st = con.createStatement();
            String sql = "select CodOdei, NomOdei from Odei";
            ResultSet rs = st.executeQuery(sql);            
            while(rs.next()) {
                Odei obj = new Odei();
                obj.setCodigo(rs.getString(1));
                obj.setNombre(rs.getString(2).toUpperCase());
                lista.add(obj);
            }
        }
        finally {
            if(st!=null) st.close();
        }
        return lista;
    }

    public List<Odei> findParaExportar(Connection con) throws SQLException {
        return findAll(con);
    }

    public DepartamentoService getDepartamentoService() {
        return departamentoService;
    }

    public void setDepartamentoService(DepartamentoService departamentoService) {
        this.departamentoService = departamentoService;
    }

    

    
    
}
